USE [HKUST_Inv_Sys]

BULK Insert [dbo].[StudentInfo]
From 'C:\PRS.csv'
WITH(
FIELDTERMINATOR =',',
ROWTERMINATOR ='\n'
)
-- To have a check the student Information
select * from [StudentInfo]

---------------------------
USE [HKUST_Inv_Sys]
GO
/****** Object:  Table [dbo].[StudentInfo]    Script Date: 08/24/2011 10:41:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StudentInfo_TEMP]') AND type in (N'U'))
BEGIN
DROP TABLE #StudentInfo_TEMP
END
GO
CREATE TABLE #StudentInfo_TEMP
(
	[Barcode] [varchar](9) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[StudentNO] [varchar](12) COLLATE Chinese_PRC_CI_AS NOT NULL,
	[Studentname] [varchar](60) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Select * from #StudentInfo_TEMP

BULK Insert #StudentInfo_TEMP
From 'C:\PRS.csv'
WITH(
FIELDTERMINATOR =',',
ROWTERMINATOR ='\n'
)
-- To have a check the student Information
INSERT INTO [dbo].[StudentInfo]([Barcode],[StudentNO],[Studentname]) SELECT [Barcode],[StudentNO],[Studentname] FROM #StudentInfo_TEMP WHERE [Barcode] NOT IN (SELECT DISTINCT [Barcode] FROM [dbo].[StudentInfo])

Select * from #StudentInfo_TEMP
Select * from [dbo].[StudentInfo]

TRUNCATE TABLE #StudentInfo_TEMP